INSERT INTO PRODUCTTYPE VALUES('P1', '티셔츠');
INSERT INTO PRODUCTTYPE VALUES('P2', '카라티');
INSERT INTO PRODUCTTYPE VALUES('P3', '남방');
INSERT INTO PRODUCTTYPE VALUES('P4', '난닝구');
INSERT INTO PRODUCTTYPE VALUES('P5', '내복');
INSERT INTO PRODUCTTYPE VALUES('P6', '셔츠');

INSERT INTO MATERIALCOLOR VALUES('M4', '검정색');
INSERT INTO MATERIALCOLOR VALUES('M5', '회색');
INSERT INTO MATERIALCOLOR VALUES('M6', '빨간색');
INSERT INTO MATERIALCOLOR VALUES('M7', '파란색');
INSERT INTO MATERIALCOLOR VALUES('M8', '핑크색');

INSERT INTO orderList VALUES(order_id.nextVal, 't-1', 95, 'c-1', 5, 100);
/*주문사항테이블*/
create table orderList(
	order_id number primary key,
	type_id varchar2(4),
	product_size number(10),
	color_id varchar2(10),
	count_button number(1),
	order_amount number,
	constraint type_id_fk foreign key(type_id) references producttype(type_id),
	constraint color_name_fk foreign key(color_id) references materialColor(material_color_id)
)

drop table orderList

/*order_id seq값*/
create sequence order_id
nocache
/*품목테이블*/
create table producttype(
	type_id varchar2(5) primary key,
	type_name varchar2(10)
)

drop table producttype

/*공정테이블*/
create table production(
	production_id number primary key,
	order_id number,
	cutting_id varchar2(4),
	dyeing_id varchar2(4),
	sewing_id varchar2(4),
	pressing_id varchar2(4),
	cutting_quality number(3),
	dyeing_quality number(3),
	sewing_quality number(3),
	pressing_quality number(3),
	cutting_time number,
	deying_time number,
	sewing_time number,
	pressing_time number,
	cutting_date varchar2(20),
	deying_date varchar2(20),
	sewing_date varchar2(20),
	pressing_date varchar2(20),
	finish_date varchar2(20),
	constraint production_order_id_fk foreign key(order_id) references orderList(order_id)
)
drop table production
create sequence production_id
nocache

/*검수테이블*/
create table outcome(
	outcome_id number primary key,
	production_id number,
	cutting_quality number(3),
	dyeing_quality number(3),
	sewing_quality number(3),
	pressing_quality number(3),
	cutting_passed varchar2(10),
	dyeing_passed varchar2(10),
	sewing_passed varchar2(10),
	pressing_passed varchar2(10),
	cutting_error varchar2(20),
	dyeing_error varchar2(20),
	sewing_error varchar2(20),
	pressing_error varchar2(20),
	outcome_date varchar2(20),
	constraint outcome_production_id_fk foreign key(production_id) references production(production_id)
)

drop table outcome

create sequence outcome_id
nocache

create table materialColor(
	material_color_id varchar2(3) primary key,
	material_color_name varchar2(10)
)
drop table materialColor

insert into MATERIALCOLOR (material_color_id, material_color_name) values('c-1', 'red');
insert into MATERIALCOLOR (material_color_id, material_color_name) values('c-2', 'blue');
insert into MATERIALCOLOR (material_color_id, material_color_name) values('c-3', 'green');
insert into MATERIALCOLOR (material_color_id, material_color_name) values('c-4', 'wine');
insert into MATERIALCOLOR (material_color_id, material_color_name) values('c-5', 'gray');

insert into PRODUCTTYPE (type_id, type_name) values('t-1', '카라');
insert into PRODUCTTYPE (type_id, type_name) values('t-2', '라운드');
insert into PRODUCTTYPE (type_id, type_name) values('t-3', 'V넥');
insert into PRODUCTTYPE (type_id, type_name) values('t-4', '셔츠');

select * from PRODUCTTYPE
select * from ORDERLIST;
select * from PRODUCTION
